﻿using ExcelDemo.Helpers;
using ExcelDemo.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using NPOI.SS.UserModel;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ExcelDemo.Controllers
{
    public class HomeController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }

        #region Npoi

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <returns></returns>
        public IActionResult ExportNpoi()
        {
            try
            {
                NpoiHelper excelHepler = new NpoiHelper();
                int rowIndex = 0;//起始坐标0
                int colIndex = 0;

                excelHepler.AddCell(rowIndex, colIndex++, "1位小数", NpoiStyleType.Title);
                excelHepler.AddCell(rowIndex, colIndex++, "日期时间", NpoiStyleType.Title);
                excelHepler.AddCell(rowIndex, colIndex++, "下拉框", NpoiStyleType.Title);
                excelHepler.AddCell(rowIndex, colIndex++, "合并2*2单元格", NpoiStyleType.Title, 1, 1);

                rowIndex++;
                colIndex = 0;
                excelHepler.AddCell(rowIndex, colIndex++, 100, NpoiStyleType.OneDecimalPlaces);
                excelHepler.AddCell(rowIndex, colIndex++, DateTime.Now, NpoiStyleType.DateTime);
                excelHepler.AddSelectCol(rowIndex, colIndex++, new string[] { "下拉框值1", "下拉框值2" });

                var fileDownloadName = $"Npoi模板.xlsx";
                return File(excelHepler.GetByte(), excelHepler.ContentType, fileDownloadName);
            }
            catch (Exception ex)
            {
                Response.ContentType = "text/plain;charset=utf-8";
                Response.WriteAsync("导出失败，请联系管理员，错误描述：" + ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 上传
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public IActionResult UploadNpoi(IFormFile file)
        {
            JsonResultModel result = new JsonResultModel();
            try
            {
                #region 验证文件格式

                if (Request.Form.Files.Count == 0)
                {
                    result.IsOk = false;
                    result.Msg = "请选择上传文件";
                    return Json(result);
                }
                string fileExt = System.IO.Path.GetExtension(file.FileName).ToLower();
                if (fileExt != ".xlsx")
                {
                    result.IsOk = false;
                    result.Msg = "上传的文件类型必须为.xlsx类型";
                    return Json(result);
                }

                #endregion 验证文件格式

                #region 获取文件数据

                IWorkbook wk = null;
                if (fileExt == ".xls")
                {
                    //把xls文件中的数据写入wk中
                    wk = new NPOI.HSSF.UserModel.HSSFWorkbook(file.OpenReadStream());
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new NPOI.XSSF.UserModel.XSSFWorkbook(file.OpenReadStream());
                }
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);
                if (sheet.LastRowNum < 1)
                {
                    result.IsOk = false;
                    result.Msg = "上传的Excel无内容";
                    return Json(result);
                }
                List<List<string>> dataList = new List<List<string>>();
                for (int rowNum = 0; rowNum < sheet.LastRowNum; rowNum++)
                {
                    var row = sheet.GetRow(rowNum);
                    if (row != null)
                    {
                        List<string> rowList = new List<string>();
                        for (int colNum = 0; colNum < row.LastCellNum; colNum++)
                        {
                            var cell = row.GetCell(colNum);
                            rowList.Add(NpoiHelper.GetCellValue(cell));
                        }
                        dataList.Add(rowList);
                    }
                }

                #endregion 获取文件数据

                result.Data = dataList;
                return Json(result);
            }
            catch (Exception ex)
            {
                result.IsOk = false;
                result.Msg = "导入出错:" + ex.Message;
            }

            return Json(result);
        }

        #endregion Npoi

        #region EPPlus 商业化，不免费

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <returns></returns>
        public IActionResult ExportEPPlus()
        {
            try
            {
                //指定EPPlus使用非商业证书,否则报错
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (ExcelPackage package = new ExcelPackage())
                {
                    ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("Sheet1");
                    int rowIndex = 1;//起始坐标1
                    int colIndex = 1;

                    #region 插入数据，坐标从1开始
                    for (int n = 0; n < 10; n++)
                    {
                        colIndex = 1;
                        EpplusHelper.AddCells(workSheet, rowIndex, colIndex, rowIndex);
                        colIndex++;
                        EpplusHelper.AddCells(workSheet, rowIndex, colIndex, $"名称{rowIndex}");
                        colIndex++;
                        EpplusHelper.AddCells(workSheet, rowIndex, colIndex, DateTime.Now);
                        colIndex++;
                        EpplusHelper.AddCells(workSheet, rowIndex, colIndex, 11);
                        colIndex++;
                        EpplusHelper.AddCells(workSheet, rowIndex, colIndex, false);
                        colIndex++;
                        EpplusHelper.AddCells(workSheet, rowIndex, colIndex, 11.66);

                        rowIndex++;
                    }

                    EpplusHelper.AddCells(workSheet, rowIndex, 1, "合并单元格", true, 2, 2);
                    rowIndex += 2;
                    #endregion 插入数据，坐标从1开始

                    //插入下拉框
                    EpplusHelper.AddCells(workSheet, rowIndex++, 1, $"下拉框");
                    EpplusHelper.AddSelectCol(workSheet, rowIndex++, 1, new string[] { "1", "2", "3" });


                    //自动宽度，填完内容后调用
                    EpplusHelper.AutoWidth(workSheet);

                    return File(package.GetAsByteArray(), EpplusHelper.ContentType, "Epplus模板.xlsx");
                }
            }
            catch (Exception ex)
            {
                Response.ContentType = "text/plain;charset=utf-8";
                Response.WriteAsync("导出失败，请联系管理员，错误描述：" + ex.Message);
                return null;
            }
        }


        /// <summary>
        /// 上传
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public IActionResult UploadEPPlus(IFormFile file)
        {
            JsonResultModel result = new JsonResultModel();
            try
            {
                #region 验证文件格式

                if (Request.Form.Files.Count == 0)
                {
                    result.IsOk = false;
                    result.Msg = "请选择上传文件";
                    return Json(result);
                }
                string fileExt = System.IO.Path.GetExtension(file.FileName).ToLower();
                if (fileExt != ".xls" && fileExt != ".xlsx")
                {
                    return Json(new
                    {
                        Success = false,
                        Message = "上传的文件类型必须为.xls或.xlsx类型"
                    });
                }

                #endregion 验证文件格式

                List<List<string>> dataList = new List<List<string>>();

                #region 获取文件数据

                //指定EPPlus使用非商业证书,否则报错
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (ExcelPackage package = new ExcelPackage(file.OpenReadStream()))
                {
                    if (package.Workbook.Worksheets.Count == 0)
                    {
                        result.IsOk = false;
                        result.Msg = "上传的文件没有工作表";
                        return Json(result);
                    }
                    var sheet = package.Workbook.Worksheets[0];
                    if (sheet.Dimension == null)
                    {
                        result.IsOk = false;
                        result.Msg = "工作表内容为空";
                        return Json(result);
                    }

                    var columnCount = sheet.Dimension.End.Column;//获取worksheet的列
                    var rowCount = sheet.Dimension.End.Row;//获取worksheet的行数
                    for (int y = 1; y <= rowCount; y++)
                    {
                        List<string> rowList = new List<string>();
                        for (int x = 1; x <= columnCount; x++)
                        {
                            if (sheet.Cells[y, x].Value == null)
                            {
                                rowList.Add("");
                            }
                            else
                            {
                                rowList.Add(sheet.Cells[y, x].Value.ToString());
                            }
                        }
                        dataList.Add(rowList);
                    }
                }

                #endregion 获取文件数据

                result.Data = dataList;
                return Json(result);
            }
            catch (Exception ex)
            {
                result.IsOk = false;
                result.Msg = "导入出错:" + ex.Message;
            }

            return Json(result);
        }

        #endregion EPPlus


    }
}
